package dao;

import conectaBD.CriaConexao;
import java.util.List;
import java.sql.*;
import java.util.ArrayList;
import negocios.Aluno;

public class AlunoDAO {

    private Connection conexao;

    public AlunoDAO() throws SQLException {
        this.conexao = (Connection) CriaConexao.getConexao();

    }

    public void adicionar(Aluno a) throws SQLException {
        // CADASTRANDO ALUNO
        String sql_a = "insert into Aluno (numero,nome, serie, turma, curso,telefone) values (?,?, ?, ?, ?,?)";
        PreparedStatement stmt = conexao.prepareStatement(sql_a);

        stmt.setString(1, a.getNumero());
        stmt.setString(2, a.getNome());
        stmt.setString(3, a.getSerie());
        stmt.setString(4, a.getTurma());
        stmt.setString(5, a.getCurso());
        stmt.setString(6, a.getTelefone());
        stmt.execute();
        stmt.close();

    }

    public List<Aluno> getList() throws SQLException {
        //Buscando Aluno
        String sql_v = "select * from Aluno";
        PreparedStatement consulta_v = conexao.prepareStatement(sql_v);
        ResultSet rs = consulta_v.executeQuery();
        List<Aluno> minhaLista = new ArrayList<Aluno>();

        while (rs.next()) {
            Aluno a1 = new Aluno();
            a1.setId(rs.getInt("id"));
            a1.setNumero(rs.getString("numero"));
            a1.setNome(rs.getString("nome"));
            a1.setSerie(rs.getString("serie"));
            a1.setTurma(rs.getString("turma"));
            a1.setCurso(rs.getString("curso"));
            a1.setTelefone(rs.getString("telefone"));
            minhaLista.add(a1);


        }

        rs.close();
        consulta_v.close();
        return minhaLista;
    }

    public void excluirAluno(Aluno a) throws SQLException {
        String sql_e = "delete from aluno where id= ?";
        PreparedStatement consulta_e = conexao.prepareStatement(sql_e);
        consulta_e.setLong(1, a.getId());
        consulta_e.execute();
        consulta_e.close();


    }

    public void altera(Aluno a) throws SQLException {
        String sql = " update Aluno set numero=?,nome=?, serie=?, turma= ?, curso=?, telefone=? where id=?";
        PreparedStatement stmt = conexao.prepareStatement(sql);
        
        stmt.setString(1, a.getNumero());
        stmt.setString(2, a.getNome());
        stmt.setString(3, a.getSerie());
        stmt.setString(4, a.getTurma());
        stmt.setString(5, a.getCurso());
        stmt.setString(6, a.getTelefone());
        stmt.setLong  (7, a.getId());
        stmt.execute();
        stmt.close();
    }

    public List<Aluno> Buscar(String nome) throws SQLException {
        String sql = "select * from Aluno where nome like ? order by serie,turma,numero";
        PreparedStatement stmt = conexao.prepareStatement(sql);
        stmt.setString(1, nome);
        ResultSet rs = stmt.executeQuery();
        List<Aluno> minhaLista = new ArrayList<Aluno>();
        while (rs.next()) {
            Aluno a1 = new Aluno();
            a1.setId(rs.getInt("id"));
            a1.setNumero(rs.getString("numero"));
            a1.setNome(rs.getString("nome"));
            a1.setSerie(rs.getString("serie"));
            a1.setTurma(rs.getString("turma"));
            a1.setCurso(rs.getString("curso"));
            a1.setTelefone(rs.getString("telefone"));
            minhaLista.add(a1);
        }
        rs.close();
        stmt.close();
        return minhaLista;
    }
}